<?php


namespace Loan\Model;

use Zend\Db\Sql\Select;
use Zend\Db\Sql\Sql;
use Zend\Db\TableGateway\TableGateway;

class LoanTable
{
    protected $tableGateway;
    protected $adapter;

    public function __construct(TableGateway $tableGateway)
    {
        $this->tableGateway = $tableGateway;
        $this->adapter = $tableGateway->getAdapter();
    }

    /**
     * 连表查询
     * @return Select
     */
    public function select()
    {
        $select = new Select();
        $select->from('pc_loan')
            ->join('pc_bank', 'pc_loan.bank_id = pc_bank.bank_id')
            ->join('pc_client', 'pc_loan.client_id = pc_client.client_id');
        return $select;
    }

    /**
     * 获取所有贷款
     * @return array
     */
    public function fetchAll()
    {
        $adapter = $this->adapter;
        $sql = new Sql($adapter);
        $select = $this->select();
        $statement = $sql->prepareStatementForSqlObject($select);
        $resultSet = $statement->execute();
        return iterator_to_array($resultSet);
    }

    /**
     * 获取某账户的所有贷款
     * @param $account_id
     * @return array
     */
    public function getLoanByAccountId($account_id)
    {
        $adapter = $this->adapter;
        $sql = new Sql($adapter);
        $select = $this->select()->where(array('account_id' => $account_id));
        $statement = $sql->prepareStatementForSqlObject($select);
        $resultSet = $statement->execute();
        return iterator_to_array($resultSet);
    }

    /**
     * 获取某客户的所有贷款
     * @param $client_id
     * @return array
     */
    public function getLoanByClientId($client_id)
    {
        $adapter = $this->adapter;
        $sql = new Sql($adapter);
        $select = $this->select()->where(array('pc_loan.client_id' => $client_id));
        $statement = $sql->prepareStatementForSqlObject($select);
        $resultSet = $statement->execute();
        return iterator_to_array($resultSet);
    }

    /**
     * 获取某客户的贷款总额
     * @param $client_id
     * @return string
     */
    public function getLoanBalanceByClientId($client_id)
    {
        $sql = "SELECT SUM(account) AS loan_balance FROM pc_loan GROUP BY client_id HAVING client_id = '" . $client_id . "';";
        $resultSet = $this->tableGateway->getAdapter()->query($sql, array());
        $result = $resultSet->current();
        return $result['loan_balance'];
    }

    /**
     * 获取某支行的所有贷款
     * @param $bank_id
     * @return array
     */
    public function getLoanByBankId($bank_id)
    {
        $adapter = $this->adapter;
        $sql = new Sql($adapter);
        $select = $this->select()->where(array('pc_bank.bank_id' => $bank_id));
        $statement = $sql->prepareStatementForSqlObject($select);
        $resultSet = $statement->execute();
        return iterator_to_array($resultSet);
    }
}